package org.odst.util;

import java.lang.reflect.InvocationTargetException;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpressionException;
import javax.xml.xpath.XPathFactory;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.odftoolkit.odfdom.OdfNamespace;
import org.odftoolkit.odfdom.doc.table.OdfTable;
import org.odftoolkit.odfdom.doc.table.OdfTableCell;
import org.odftoolkit.odfdom.doc.table.OdfTableRow;
import org.odftoolkit.odfdom.dom.OdfNamespaceNames;
import org.odftoolkit.odfdom.dom.element.table.TableTableElement;
import org.odftoolkit.odfdom.dom.element.text.TextPElement;
import org.odst.parser.Cell;
import org.odst.transformer.RowCollection;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

/**
 * This class contains many utility methods used by jXLS framework
 * 
 * @author Leonid Vysochyn
 * @author Vincent Dutat
 * @author Cyril PODER
 */
public final class Util {
	protected static Log log = LogFactory.getLog(Util.class);

	private static final String[][] ENTITY_ARRAY = { { "quot", "34" }, // " -
			// double
			// -
			// quote
			{ "amp", "38" }, // & - ampersand
			{ "lt", "60" }, // < - less-than
			{ "gt", "62" }, // > - greater-than
			{ "apos", "39" } // XML apostrophe
	};

	private static Map<String, String> xmlEntities = new HashMap<String, String>();

	static {
		for (int i = 0; i < ENTITY_ARRAY.length; i++) {
			xmlEntities.put(ENTITY_ARRAY[i][1], ENTITY_ARRAY[i][0]);
		}
	}

	public static void removeRowCollectionPropertiesFromRow(
			RowCollection rowCollection) {
		OdfTableRow currentRow = rowCollection.getParentRow().getOooRow();
		for (int i = 0; i <= rowCollection.getDependentRowNumber(); i++) {
			XPath xpath = XPathFactory.newInstance().newXPath();
			xpath.setNamespaceContext(OdfNamespace.newNamespace(OdfNamespaceNames.OFFICE));
			try {
				NodeList textElements = (NodeList)xpath.evaluate(".//text:p", currentRow.getOdfElement(), XPathConstants.NODESET);
				for (int j=0;j<textElements.getLength();j++) {
					TextPElement textPElement = (TextPElement)textElements.item(j); 
					String regex = "[-+*/().A-Za-z_0-9\\s]*";
					String cellValue = textPElement.getTextContent();
					String strToReplace = "\\$\\{" + regex
							+ rowCollection
							.getCollectionProperty().getFullCollectionName().replaceAll("\\.", "\\\\.") + "\\." + regex
							+ "\\}";
					textPElement.setTextContent(cellValue.replaceAll(strToReplace, ""));
				}
			} catch (XPathExpressionException e) {
				e.printStackTrace();
			}
			currentRow = currentRow.getNextRow();
		}
	}

	private static void removeRowCollectionPropertyFromCell(OdfTableCell cell,
			String collectionName) {
		String regex = "[-+*/().A-Za-z_0-9\\s]*";
		if (cell != null /*&& cell.getType().equals(CellContentType.TEXT)*/) {
			String cellValue = cell.getStringValue();
			String strToReplace = "\\$\\{" + regex
					+ collectionName.replaceAll("\\.", "\\\\.") + "\\." + regex
					+ "\\}";
			cell.setStringValue(cellValue.replaceAll(strToReplace, ""));
		}
	}

	/**
	 * Removes merged region from sheet
	 * 
	 * @param sheet
	 * @param region
	 */
//	private static void removeMergedRegion(OdfTable sheet,
//			CellRangeAddress region) {
////		XMergeable mergeable = (XMergeable)UnoRuntime.queryInterface(XMergeable.class, region);
////		mergeable.merge(false);
//	}

	/**
	 * returns merged region index
	 * 
	 * @param sheet
	 * @param mergedRegion
	 * @return index of mergedRegion or -1 if the region not found
	 */
//	private static int getMergedRegionIndex(XSpreadsheet sheet,
//			XMergeable mergedRegion) {
//		int result = 0;
//		XSheetCellRanges sheetCellRanges = (XSheetCellRanges)UnoRuntime.queryInterface(XSheetCellRanges.class, sheet);
//		for(int j=0; j < sheetCellRanges.getCount(); j++) {
//			XMergeable mergeable = null;
//			try {
//				mergeable = (XMergeable)UnoRuntime.queryInterface(XMergeable.class, sheetCellRanges.getByIndex(j));
//			} catch (IndexOutOfBoundsException e) {
//				e.printStackTrace();
//			} catch (WrappedTargetException e) {
//				e.printStackTrace();
//			}
//			if (mergeable.getIsMerged()) {
//				if (mergeable.equals(mergedRegion))
//					return result;
//				result ++;
//			}
//		}
//		return -1;
//	}

//	public static boolean areRegionsEqual(XCellRange region1,
//			XCellRange region2) {
//		if ((region1 == null && region2 != null)
//				|| (region1 != null && region2 == null)) {
//			return false;
//		}
//		if (region1 == null && region2 == null) {
//			return true;
//		}
//		XCellRangeAddressable cellAddressable1 = (XCellRangeAddressable)UnoRuntime.queryInterface(XCellRangeAddressable.class, region1);
//		XCellRangeAddressable cellAddressable2 = (XCellRangeAddressable)UnoRuntime.queryInterface(XCellRangeAddressable.class, region2);
//		return (cellAddressable1.getRangeAddress().StartColumn == cellAddressable2.getRangeAddress().StartColumn
//				&& cellAddressable1.getRangeAddress().EndColumn == cellAddressable2.getRangeAddress().EndColumn
//				&& cellAddressable1.getRangeAddress().StartRow == cellAddressable2.getRangeAddress().StartRow
//				&& cellAddressable1.getRangeAddress().EndRow == cellAddressable2.getRangeAddress().EndRow);
//	}

//	private static XMergeable getMergedRegion(XSpreadsheet sheet, int i) {
//		XSheetCellRanges sheetCellRanges = (XSheetCellRanges)UnoRuntime.queryInterface(XSheetCellRanges.class, sheet);
//		for(int j=0; j < sheetCellRanges.getCount(); j++) {
//			XMergeable mergeable = null;
//			try {
//				mergeable = (XMergeable)UnoRuntime.queryInterface(XMergeable.class, sheetCellRanges.getByIndex(j));
//			} catch (IndexOutOfBoundsException e) {
//				e.printStackTrace();
//			} catch (WrappedTargetException e) {
//				e.printStackTrace();
//			}
//			if (mergeable.getIsMerged()) {
//				if (i == 0)
//					return mergeable;
//				i--;
//			}
//		}
//		return null;
//	}

//	private static boolean isNewMergedRegion(XCellRange region,
//			Collection mergedRegions) {
//		for (Iterator iterator = mergedRegions.iterator(); iterator.hasNext();) {
//			XCellRange cellRangeAddress = (XCellRange) iterator
//					.next();
//			if (areRegionsEqual(cellRangeAddress, region)) {
//				return false;
//			}
//		}
//		return true;
//	}

//	public static CellRangeAddress getMergedRegion(XSpreadsheet sheet,
//			int rowNum, int cellNum) {
//		for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
//			CellRangeAddress merged = getMergedRegion(sheet, i);
//			if (isRangeContainsCell(merged, rowNum, cellNum)) {
//				return merged;
//			}
//		}
//		return null;
//	}

//	public static boolean isRangeContainsCell(XCellRange range, int row,
//			int col) {
//		XCellRangeAddressable cellAddressable = (XCellRangeAddressable)UnoRuntime.queryInterface(XCellRangeAddressable.class, range);
//		if ((cellAddressable.getRangeAddress().StartRow <= row) && (cellAddressable.getRangeAddress().EndRow >= row)
//				&& (cellAddressable.getRangeAddress().StartColumn <= col)
//				&& (cellAddressable.getRangeAddress().EndColumn >= col)) {
//			return true;
//		}
//		return false;
//	}

//	public static boolean removeMergedRegion(XSpreadsheet sheet, int rowNum,
//			int cellNum) {
//		Set mergedRegionNumbersToRemove = new TreeSet();
//		for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
//			CellRangeAddress merged = getMergedRegion(sheet, i);
//			if (isRangeContainsCell(merged, rowNum, cellNum)) {
//				mergedRegionNumbersToRemove.add(new Integer(i));
//			}
//		}
//		for (Iterator iterator = mergedRegionNumbersToRemove.iterator(); iterator
//				.hasNext();) {
//			Integer regionNumber = (Integer) iterator.next();
//			sheet.removeMergedRegion(regionNumber.intValue());
//		}
//		return !mergedRegionNumbersToRemove.isEmpty();
//	}

	public static void prepareCollectionPropertyInRowForDuplication(
			RowCollection rowCollection, String collectionItemName) {
		OdfTableRow currentRow = rowCollection.getParentRow().getOooRow();
		for (int i = 0; i <= rowCollection.getDependentRowNumber(); i++) {
			XPath xpath = XPathFactory.newInstance().newXPath();
			xpath.setNamespaceContext(OdfNamespace.newNamespace(OdfNamespaceNames.OFFICE));
			try {
				NodeList textElements = (NodeList)xpath.evaluate(".//text:p", currentRow.getOdfElement(), XPathConstants.NODESET);
				for (int j=0;j<textElements.getLength();j++) {
					TextPElement textPElement = (TextPElement)textElements.item(j); 
					textPElement.setTextContent(replaceCollectionProperty(textPElement.getTextContent(), rowCollection.getCollectionProperty()
								.getFullCollectionName(), collectionItemName));
				}
			} catch (XPathExpressionException e) {
				e.printStackTrace();
			}
			currentRow = currentRow.getNextRow();
		}
	}

	private static void prepareCollectionPropertyInCellForDuplication(
			OdfTableCell cell, String collectionName, String collectionItemName) {
		if (cell != null && cell.getValueType() != null && cell.getValueType().equals("string")) {
			String cellValue = cell.getStringValue();
			String newValue = replaceCollectionProperty(cellValue,
					collectionName, collectionItemName);
			// String newValue = cellValue.replaceFirst(collectionName,
			// collectionItemName);
			cell.setStringValue(newValue);
		}
	}

	private static String replaceCollectionProperty(String property,
			String collectionName, String newValue) {
		return property.replaceAll(collectionName, newValue);
	}

	public static void prepareCollectionPropertyInRowForContentDuplication(
			RowCollection rowCollection) {
		for (int i = 0; i < rowCollection.getCells().size(); i++) {
			Cell cell = (Cell) rowCollection.getCells().get(i);
			prepareCollectionPropertyInCellForDuplication(cell.getOooCell(),
					rowCollection.getCollectionProperty()
							.getFullCollectionName(), rowCollection
							.getCollectionItemName());
		}
	}

	public static void duplicateRowCollectionProperty(
			RowCollection rowCollection) {
		Collection<?> collection = rowCollection.getCollectionProperty()
				.getCollection();
		int rowNum = rowCollection.getParentRow().getOooRow().getRowIndex();
		OdfTable sheet = rowCollection.getParentRow().getSheet()
				.getOooSheet();
		if (collection.size() > 1) {
			for (int i = 1; i < collection.size(); i++) {
				int destRow = rowNum + i;
				for (int j = 0; j < rowCollection.getCells().size(); j++) {
					Cell cell = (Cell) rowCollection.getCells().get(j);
					if (!cell.isEmpty()) {
						OdfTableCell destCell;
						try {
							destCell = sheet.getCellByPosition(cell.getOooCell().getColumnIndex(), destRow);
							copyCell(sheet.getCellByPosition(cell.getOooCell().getColumnIndex(), rowNum), destCell, false);
						} catch (IndexOutOfBoundsException e) {
							e.printStackTrace();
						}
					}
				}
			}
		}
	}

	public static int duplicateRow(RowCollection rowCollection) {
		Collection<?> collection = rowCollection.getCollectionProperty()
				.getCollection();
		int row = rowCollection.getParentRow().getOooRow().getRowIndex();
		OdfTable sheet = rowCollection.getParentRow().getSheet()
				.getOooSheet();
		
		if (collection.size() > 1) {
			if (rowCollection.getDependentRowNumber() == 0) {
				Node src = sheet.getRowByIndex(row).getOdfElement();
				Node target = src.getNextSibling();
				TableTableElement tableElement = sheet.getOdfElement();
				for (int i=0;i<collection.size() - 1;i++) {
					Node newRowElement = src.cloneNode(true);
						tableElement.insertBefore(newRowElement, target);
				}
//				for (int i=row;i<row+collection.size()-1;i++)
//					for (int j=0;j<src.getCellCount();j++)
//						copyCell(src.getCellByIndex(j), sheet.getCellByPosition(j, i), true);
//				try {
//					sheet.getWorkbook().copyRange(sheet.getCellRangeByPosition(0, row + collection.size() -1 , sheet.getLastColNum(), row + collection.size() - 1), sheet.getCellRangeByPosition(0, row , sheet.getLastColNum(), row + collection.size() - 2));
//				} catch (IndexOutOfBoundsException e) {
//					e.printStackTrace();
//				}
			} else {
//				for (int i = 0; i < collection.size() - 1; i++) {
//					try {
//						XCellRange rowToInsert = (XCellRange)UnoRuntime.queryInterface(XCellRange.class, sheet.getRow(row+rowCollection.getDependentRowNumber()));
//						XCellRangeAddressable cellRangeAddressable = (XCellRangeAddressable)UnoRuntime.queryInterface(XCellRangeAddressable.class, rowToInsert);
//						sheet.insertCells(cellRangeAddressable.getRangeAddress(), CellInsertMode.DOWN);
//						XCellRange rowToDuplicate = (XCellRange)UnoRuntime.queryInterface(XCellRange.class, sheet.getRow(row));
//						XCellAddressable cellAddressable = (XCellAddressable)UnoRuntime.queryInterface(XCellAddressable.class, sheet.getCellByPosition(0, row + 1));
//						cellRangeAddressable = (XCellRangeAddressable)UnoRuntime.queryInterface(XCellRangeAddressable.class, rowToDuplicate);
//						sheet.copyRange(cellAddressable.getCellAddress(), cellRangeAddressable.getRangeAddress());
//					} catch (IndexOutOfBoundsException e) {
//						e.printStackTrace();
//					}
//					sheet.createRows(row, rowCollection.getDependentRowNumber() + 1);
//					shiftCopyRowCollection(rowCollection);
//				}
//				shiftUncoupledCellsUp(rowCollection);
			}
		}
//		try {
//			rowCollection.getParentRow().getSheet().getHssfWorkbook().save("temp_" + sheet.getTableName() + "_row_dup.ods");
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
		return (collection.size() - 1)
				* (rowCollection.getDependentRowNumber() + 1);
	}

//	private static void shiftCopyRowCollection(RowCollection rowCollection) {
//		Sheet XSpreadsheet = rowCollection.getParentRow().getSheet()
//				.getHssfSheet();
//		int startRow = rowCollection.getParentRow().getHssfRow().getRangeAddress().StartRow;
//		int num = rowCollection.getDependentRowNumber();
//		shiftRows(XSpreadsheet, startRow + num + 1, XSpreadsheet.getLastRowNum(),
//				num + 1);
//		copyRowCollection(rowCollection);
//	}

//	private static void copyRowCollection(RowCollection rowCollection) {
//		OdfTable sheet = rowCollection.getParentRow().getSheet()
//				.getHssfSheet();
//		int from = rowCollection.getParentRow().getHssfRow().getRowIndex();
//		int num = rowCollection.getDependentRowNumber() + 1;
//		int to = from + num;
////		Set mergedRegions = new HashSet();
//		for (int i = from; i < from + num; i++) {
//			OdfTableRow srcRow = sheet.getRowByIndex(i);
//			OdfTableRow destRow = sheet.getRowByIndex(to + i - from);
//			if (srcRow.getHeight() >= 0) {
//				destRow.setHeight(srcRow.getHeight(), true);
//			}
//			for (int j = 0; j < srcRow.getCellCount(); j++) {
//				try {
//					OdfTableCell srcCell = srcRow.getCellByIndex(j);
//					if (srcCell != null) {
//						copyCell(srcCell, destRow.getCellByIndex(j), true);
//					}
//				} catch (IndexOutOfBoundsException e) {
//					e.printStackTrace();
//				}
//			}
//		}
//	}

//	private static void shiftUncoupledCellsUp(RowCollection rowCollection) {
//		Row row = rowCollection.getParentRow();
//		if (row.getCells().size() > rowCollection.getCells().size()) {
//			for (int i = 0; i < row.getCells().size(); i++) {
//				Cell cell = (Cell) row.getCells().get(i);
//				if (!rowCollection.containsCell(cell)) {
//					shiftColumnUp(cell, row.getHssfRow()
//							+ rowCollection.getCollectionProperty()
//									.getCollection().size(), rowCollection
//							.getCollectionProperty().getCollection().size() - 1);
//				}
//			}
//		}
//	}
//
//	private static void shiftColumnUp(Cell cell, int startRow, int shiftNumber) {
//		XSpreadsheet sheet = cell.getRow().getSheet().getHssfSheet();
//		int cellNum = cell.getHssfCell().getColumnIndex();
//		List hssfMergedRegions = new ArrayList();
//		// find all merged regions in this area
//		for (int i = startRow; i <= sheet.getLastRowNum(); i++) {
//			CellRangeAddress region = getMergedRegion(sheet, i, cellNum);
//			if (region != null && isNewMergedRegion(region, hssfMergedRegions)) {
//				hssfMergedRegions.add(region);
//			}
//		}
//		// move all related cells up
//		for (int i = startRow; i <= sheet.getLastRowNum(); i++) {
//			if (sheet.getRow(i).getCell(cellNum) != null) {
//				HSSFCell destCell = sheet.getRow(i - shiftNumber).getCell(
//						cellNum);
//				if (destCell == null) {
//					destCell = sheet.getRow(i - shiftNumber)
//							.createCell(cellNum);
//				}
//				moveCell(sheet.getRow(i).getCell(cellNum), destCell);
//			}
//		}
//		// remove previously shifted merged regions in this area
//		for (Iterator iterator = hssfMergedRegions.iterator(); iterator
//				.hasNext();) {
//			removeMergedRegion(sheet, (CellRangeAddress) iterator.next());
//		}
//		// set merged regions for shifted cells
//		for (Iterator iterator = hssfMergedRegions.iterator(); iterator
//				.hasNext();) {
//			CellRangeAddress region = (CellRangeAddress) iterator.next();
//			CellRangeAddress newRegion = new CellRangeAddress(region
//					.getFirstRow()
//					- shiftNumber, region.getLastRow() - shiftNumber, region
//					.getFirstColumn(), region.getLastColumn());
//			sheet.addMergedRegion(newRegion);
//		}
//		// remove moved cells
//		int i = sheet.getLastRowNum();
//		while (sheet.getRow(i).getCell(cellNum) == null && i >= startRow) {
//			i--;
//		}
//		for (int j = 0; j < shiftNumber && i >= startRow; j++, i--) {
//			if (sheet.getRow(i).getCell(cellNum) != null) {
//				sheet.getRow(i).removeCell(sheet.getRow(i).getCell(cellNum));
//			}
//		}
//	}

//	private static void moveCell(HSSFCell srcCell, HSSFCell destCell) {
//		destCell.setCellStyle(srcCell.getCellStyle());
//		switch (srcCell.getCellType()) {
//		case HSSFCell.CELL_TYPE_STRING:
//			destCell.setCellValue(srcCell.getRichStringCellValue());
//			break;
//		case HSSFCell.CELL_TYPE_NUMERIC:
//			destCell.setCellValue(srcCell.getNumericCellValue());
//			break;
//		case HSSFCell.CELL_TYPE_BLANK:
//			destCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
//			break;
//		case HSSFCell.CELL_TYPE_BOOLEAN:
//			destCell.setCellValue(srcCell.getBooleanCellValue());
//			break;
//		case HSSFCell.CELL_TYPE_ERROR:
//			destCell.setCellErrorValue(srcCell.getErrorCellValue());
//			break;
//		case HSSFCell.CELL_TYPE_FORMULA:
//			break;
//		default:
//			break;
//		}
//		srcCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
//	}

//	private static void duplicateStyle(RowCollection rowCollection,
//			int rowToCopy, int startRow, int num) {
//		XSpreadsheet sheet = rowCollection.getParentRow().getSheet()
//				.getXSpreadsheet();
//		Set mergedRegions = new HashSet();
//		int srcRow = sheet.getRow(rowToCopy);
//		for (int i = startRow; i < startRow + num; i++) {
//			int destRow = sheet.getRow(i);
//			if (destRow == null) {
//				destRow = sheet.createRow(i);
//			}
//			if (srcRow.getHeight() >= 0) {
//				destRow.setHeight(srcRow.getHeight());
//			}
//			for (int j = 0; j < rowCollection.getCells().size(); j++) {
//				Cell cell = (Cell) rowCollection.getCells().get(j);
//				HSSFCell hssfCell = cell.getHssfCell();
//				if (hssfCell != null) {
//					HSSFCell newCell = destRow.createCell(hssfCell
//							.getColumnIndex());
//					copyCell(hssfCell, newCell, true);
//					CellRangeAddress mergedRegion = getMergedRegion(sheet,
//							rowToCopy, hssfCell.getColumnIndex());
//					if (mergedRegion != null) {
//						CellRangeAddress newMergedRegion = new CellRangeAddress(
//								i, i + mergedRegion.getLastRow()
//										- mergedRegion.getFirstRow(),
//								mergedRegion.getFirstColumn(), mergedRegion
//										.getLastColumn());
//						if (isNewMergedRegion(newMergedRegion, mergedRegions)) {
//							mergedRegions.add(newMergedRegion);
//							sheet.addMergedRegion(newMergedRegion);
//						}
//					}
//				}
//			}
//		}
//	}

//	public static void copyRow(Sheet sheet, com.bfinance.framework.openoffice.Row oldRow, com.bfinance.framework.openoffice.Row newRow) {
//		Set mergedRegions = new HashSet();
//		XColumnRowRange columnRowRange = (XColumnRowRange)UnoRuntime.queryInterface(XColumnRowRange.class, sheet);
//		if (oldRow.getHeight() >= 0) {
//			newRow.setHeight(oldRow.getHeight());
//		}
//		for (int j = oldRow.getFirstCell().getCellAddress().Column; j <= oldRow.getLastCell().getCellAddress().Column; j++) {
//			try {
//				com.bfinance.framework.openoffice.Cell oldCell = oldRow.getCell(j);
//				com.bfinance.framework.openoffice.Cell newCell = newRow.getCell(j);
//				if (oldCell != null) {
//					copyCell(oldCell, newCell, true);
////					CellRangeAddress mergedRegion = getMergedRegion(sheet, oldRow
////							.getRowNum(), oldCell.getColumnIndex());
////					if (mergedRegion != null) {
////						CellRangeAddress newMergedRegion = new CellRangeAddress(
////								newRow.getRowNum(), newRow.getRowNum()
////										+ mergedRegion.getLastRow()
////										- mergedRegion.getFirstRow(), mergedRegion
////										.getFirstColumn(), mergedRegion
////										.getLastColumn());
////						if (isNewMergedRegion(newMergedRegion, mergedRegions)) {
////							mergedRegions.add(newMergedRegion);
////							sheet.addMergedRegion(newMergedRegion);
////						}
////					}
//				}
//			} catch (IndexOutOfBoundsException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//		}
//	}

//	public static void copyRow(Sheet srcSheet, Sheet destSheet,
//			int srcRowNum, int destRowNum) {
//		com.bfinance.framework.openoffice.Row srcRow = srcSheet.getRow(srcRowNum);
//		com.bfinance.framework.openoffice.Row destRow = destSheet.getRow(destRowNum);
//		srcSheet.copyRange(new CellAddress(destRow.getRangeAddress().Sheet, destRow.getRangeAddress().StartColumn, destRow.getRangeAddress().StartRow), srcRow.getRangeAddress());
////		Set mergedRegions = new TreeSet();
////		if (srcRow.getHeight() >= 0) {
////			destRow.setHeight(srcRow.getHeight());
////		}
////		for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
////			HSSFCell oldCell = srcRow.getCell(j);
////			HSSFCell newCell = destRow.getCell(j);
////			if (oldCell != null) {
////				if (newCell == null) {
////					newCell = destRow.createCell(j);
////				}
////				copyCell(oldCell, newCell, true);
////				CellRangeAddress mergedRegion = getMergedRegion(srcSheet,
////						srcRow.getRowNum(), oldCell.getColumnIndex());
////				if (mergedRegion != null) {
////					// Region newMergedRegion = new Region( destRow.getRowNum(),
////					// mergedRegion.getColumnFrom(),
////					// destRow.getRowNum() + mergedRegion.getRowTo() -
////					// mergedRegion.getRowFrom(), mergedRegion.getColumnTo() );
////					CellRangeAddress newMergedRegion = new CellRangeAddress(
////							mergedRegion.getFirstRow(), mergedRegion
////									.getLastRow(), mergedRegion
////									.getFirstColumn(), mergedRegion
////									.getLastColumn());
////					if (isNewMergedRegion(newMergedRegion, mergedRegions)) {
////						mergedRegions.add(newMergedRegion);
////						destSheet.addMergedRegion(newMergedRegion);
////					}
////				}
////			}
////		}
//	}

//	public static void copyRow(Sheet srcSheet, Sheet destSheet,
//			int srcRowNum, int destRowNum, String expressionToReplace,
//			String expressionReplacement) {
//		copyRow(srcSheet, destSheet, srcRowNum, destRowNum);
//		com.bfinance.framework.openoffice.Row destRow = destSheet.getRow(destRowNum);
//		destRow.replace(expressionToReplace, expressionReplacement);
////		Set mergedRegions = new HashSet();
////		if (srcRow.getHeight() >= 0) {
////			destRow.setHeight(srcRow.getHeight());
////		}
////		for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
////			HSSFCell oldCell = srcRow.getCell(j);
////			HSSFCell newCell = destRow.getCell(j);
////			if (oldCell != null) {
////				if (newCell == null) {
////					newCell = destRow.createCell(j);
////				}
////				copyCell(oldCell, newCell, true, expressionToReplace,
////						expressionReplacement);
////				CellRangeAddress mergedRegion = getMergedRegion(srcSheet,
////						srcRow.getRowNum(), oldCell.getColumnIndex());
////				if (mergedRegion != null) {
////					// Region newMergedRegion = new Region( destRow.getRowNum(),
////					// mergedRegion.getColumnFrom(),
////					// destRow.getRowNum() + mergedRegion.getRowTo() -
////					// mergedRegion.getRowFrom(), mergedRegion.getColumnTo() );
////					CellRangeAddress newMergedRegion = new CellRangeAddress(
////							mergedRegion.getFirstRow(), mergedRegion
////									.getLastRow(), mergedRegion
////									.getFirstColumn(), mergedRegion
////									.getLastColumn());
////					if (isNewMergedRegion(newMergedRegion, mergedRegions)) {
////						mergedRegions.add(newMergedRegion);
////						destSheet.addMergedRegion(newMergedRegion);
////					}
////				}
////			}
////		}
//	}

//	public static void copySheets(XSpreadsheet newSheet, XSpreadsheet sheet) {
//		int maxColumnNum = 0;
//		for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
//			int srcRow = sheet.getRow(i);
//			int destRow = newSheet.createRow(i);
//			if (srcRow != null) {
//				Util.copyRow(sheet, newSheet, srcRow, destRow);
//				if (srcRow.getLastCellNum() > maxColumnNum) {
//					maxColumnNum = srcRow.getLastCellNum();
//				}
//			}
//		}
//		for (int i = 0; i <= maxColumnNum; i++) {
//			newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
//		}
//	}

//	public static void copySheets(XSpreadsheet newSheet, XSpreadsheet sheet,
//			String expressionToReplace, String expressionReplacement) {
//		int maxColumnNum = 0;
//		for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
//			int srcRow = sheet.getRow(i);
//			int destRow = newSheet.createRow(i);
//			if (srcRow != null) {
//				Util.copyRow(sheet, newSheet, srcRow, destRow,
//						expressionToReplace, expressionReplacement);
//				if (srcRow.getLastCellNum() > maxColumnNum) {
//					maxColumnNum = srcRow.getLastCellNum();
//				}
//			}
//		}
//		for (int i = 0; i <= maxColumnNum; i++) {
//			newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
//		}
//	}

	public static void copyCell(OdfTableCell srcCell, OdfTableCell destCell,
			boolean copyStyle) {
		Node row = srcCell.getOdfElement().getParentNode();
		Node newCellNode = srcCell.getOdfElement().cloneNode(true);
		Node oldCellNode = destCell.getOdfElement();
		row.insertBefore(newCellNode, oldCellNode);
		row.removeChild(oldCellNode);
//		if (oldCell != null) {
//			if (copyStyle)
//				copyStyle(oldCell, newCell);
//			if (oldCell.getValueType() != null) {
//				newCell.setValueType(oldCell.getValueType());
//				if (oldCell.getValueType().equals("string"))
//					newCell.setStringValue(oldCell.getStringValue());
//				else if (oldCell.getValueType().equals("boolean"))
//					newCell.setBooleanValue(oldCell.getBooleanValue());
//		//		else if (oldCell.getDateValue() != null)
//		//			newCell.setDateValue(oldCell.getDateValue());
//		//		else if (oldCell.getTimeValue() != null)
//		//			newCell.setTimeValue(oldCell.getTimeValue());
//		//		else if (oldCell.getDoubleValue() != null)
//		//			newCell.setDoubleValue(oldCell.getDoubleValue());
//				else if (oldCell.getValueType().equals("double"))
//					newCell.setDoubleValue(oldCell.getDoubleValue());
//				else if (oldCell.getValueType().equals("formula"))
//					newCell.setFormula(oldCell.getFormula());
//				else
//					System.out.println("unknown type " + oldCell.getValueType());
//			}
//		}
	}

	private static void copyStyle(OdfTableCell oldCell, OdfTableCell newCell) {
		if (oldCell.getStyleName() != null) {
			String value = newCell.getValueType() != null && newCell.getValueType().equals("string") && newCell.getStringValue() != null ? newCell.getStringValue() : "";
			newCell.setDisplayText(value, oldCell.getStyleName());
		}
		try {
			if (oldCell.getCellBackgroundColor() != null)
				newCell.setCellBackgroundColor(oldCell.getCellBackgroundColor());
		} catch(IllegalArgumentException e) {}
		if (oldCell.getHorizontalAlignment() != null)
			newCell.setHorizontalAlignment(oldCell.getHorizontalAlignment());
		newCell.setTextWrapped(oldCell.isTextWrapped());
		if (oldCell.getVerticalAlignment() != null)
			newCell.setVerticalAlignment(oldCell.getVerticalAlignment());
		if (oldCell.getFormatString() != null)
			newCell.setFormatString(oldCell.getFormatString());
	}

	public static void copyCell(OdfTableCell oldCell, OdfTableCell newCell,
			boolean copyStyle, String expressionToReplace,
			String expressionReplacement) {
		if (copyStyle)
			copyStyle(oldCell, newCell);
		if (oldCell.getStringValue() != null)
			newCell.setStringValue(oldCell.getStringValue().replaceAll(
					expressionToReplace, expressionReplacement));
		if (oldCell.getBooleanValue() != null)
			newCell.setBooleanValue(oldCell.getBooleanValue());
		if (oldCell.getDateValue() != null)
			newCell.setDateValue(oldCell.getDateValue());
		if (oldCell.getTimeValue() != null)
			newCell.setTimeValue(oldCell.getTimeValue());
		if (oldCell.getDoubleValue() != null)
			newCell.setDoubleValue(oldCell.getDoubleValue());
		if (oldCell.getFormula() != null)
			newCell.setFormula(oldCell.getFormula());
	}

	public static Object getProperty(Object bean, String propertyName) {
		Object value = null;
		try {
			if (log.isDebugEnabled()) {
				log.debug("getting property=" + propertyName + " for bean="
						+ bean.getClass().getName());
			}
			value = PropertyUtils.getProperty(bean, propertyName);
		} catch (IllegalAccessException e) {
			log.warn("Can't get property " + propertyName + " in the bean "
					+ bean, e);
		} catch (InvocationTargetException e) {
			log.warn("Can't get property " + propertyName + " in the bean "
					+ bean, e);
		} catch (NoSuchMethodException e) {
			log.warn("Can't get property " + propertyName + " in the bean "
					+ bean, e);
		}
		return value;

	}

	/**
	 * Saves workbook to file
	 * 
	 * @param fileName
	 *            - File name to save workbook
	 * @param workbook
	 *            - Workbook to save
	 */
//	public static void writeToFile(String fileName, HSSFWorkbook workbook) {
//		OutputStream os;
//		try {
//			os = new BufferedOutputStream(new FileOutputStream(fileName));
//			workbook.write(os);
//			os.flush();
//			os.close();
//		} catch (IOException e) {
//			e.printStackTrace();
//		}
//	}

	/**
	 * Duplicates given HSSFCellStyle object
	 * 
	 * @param workbook
	 *            - source HSSFWorkbook object
	 * @param style
	 *            - HSSFCellStyle object to duplicate
	 * @return HSSFCellStyle
	 */
//	public static XStyle duplicateStyle(Workbook workbook,
//			XStyle style) {
//		HSSFCellStyle newStyle = workbook.createCellStyle();
//		newStyle.setAlignment(style.getAlignment());
//		newStyle.setBorderBottom(style.getBorderBottom());
//		newStyle.setBorderLeft(style.getBorderLeft());
//		newStyle.setBorderRight(style.getBorderRight());
//		newStyle.setBorderTop(style.getBorderTop());
//		newStyle.setBottomBorderColor(style.getBottomBorderColor());
//		newStyle.setDataFormat(style.getDataFormat());
//		newStyle.setFillBackgroundColor(style.getFillBackgroundColor());
//		newStyle.setFillForegroundColor(style.getFillForegroundColor());
//		newStyle.setFillPattern(style.getFillPattern());
//		newStyle.setFont(workbook.getFontAt(style.getFontIndex()));
//		newStyle.setHidden(style.getHidden());
//		newStyle.setIndention(style.getIndention());
//		newStyle.setLeftBorderColor(style.getLeftBorderColor());
//		newStyle.setLocked(style.getLocked());
//		newStyle.setRightBorderColor(style.getRightBorderColor());
//		newStyle.setTopBorderColor(style.getTopBorderColor());
//		newStyle.setVerticalAlignment(style.getVerticalAlignment());
//		newStyle.setWrapText(style.getWrapText());
//		return newStyle;
//	}

	public static String escapeAttributes(String tag) {
		if (tag == null) {
			return tag;
		}
		int i = 0;
		StringBuffer sb = new StringBuffer("");
		StringBuffer attrValue = new StringBuffer("");
		final char expressionClosingSymbol = '}';
		final char expressionStartSymbol = '{';
		boolean isAttrValue = false;
		int exprCount = 0;
		while (i < tag.length()) {
			if (!isAttrValue) {
				sb.append(tag.charAt(i));
				if (tag.charAt(i) == '\"') {
					isAttrValue = true;
					attrValue = new StringBuffer("");
				}
			} else {
				if (tag.charAt(i) == '\"') {
					if (exprCount != 0) {
						attrValue.append(tag.charAt(i));
					} else {
						sb.append(escapeXml(attrValue.toString()));
						sb.append(tag.charAt(i));
						isAttrValue = false;
					}
				} else {
					attrValue.append(tag.charAt(i));
					if (tag.charAt(i) == expressionClosingSymbol) {
						exprCount--;
					} else if (tag.charAt(i) == expressionStartSymbol) {
						exprCount++;
					}
				}
			}
			i++;
		}
		if (isAttrValue) {
			log.warn("Can't parse ambiguous quot in " + tag);
		}
		return sb.toString();
	}

	/**
	 * <p>
	 * Escapes XML entities in a <code>String</code>.
	 * </p>
	 * 
	 * @param str
	 *            The <code>String</code> to escape.
	 * @return A new escaped <code>String</code>.
	 */
	private static String escapeXml(String str) {
		if (str == null) {
			return str;
		}
		StringBuffer buf = new StringBuffer(str.length() * 2);
		int i;
		for (i = 0; i < str.length(); ++i) {
			char ch = str.charAt(i);
			String entityName = getEntityName(ch);
			if (entityName == null) {
				if (ch > 0x7F) {
					buf.append("&#");
					buf.append((int) ch);
					buf.append(';');
				} else {
					buf.append(ch);
				}
			} else {
				buf.append('&');
				buf.append(entityName);
				buf.append(';');
			}
		}
		return buf.toString();
	}

	private static String getEntityName(char ch) {
		return (String) xmlEntities.get(Integer.toString(ch));
	}

	public static void shiftCellsLeft(OdfTable sheet, int startRow,
			int startCol, int endRow, int endCol, int shiftNumber) {
//		try {
			OdfTableRow currentRow = sheet.getRowByIndex(startRow);
			while (currentRow != null && currentRow.getRowIndex() <= endRow) {
				for (int i=0;i<shiftNumber;i++) {
					OdfTableCell oldCell = currentRow.getCellByIndex(endCol);
					OdfTableCell newCell = currentRow.getCellByIndex(startCol - shiftNumber);
					Node newNode = oldCell.getOdfElement();
					currentRow.getOdfElement().removeChild(newNode);
					currentRow.getOdfElement().insertBefore(newNode, newCell.getOdfElement());
//					copyCell(currentRow.getCellByIndex(i + shiftNumber), currentRow.getCellByIndex(i), true);
				}
				currentRow = currentRow.getNextRow();
			}
//			CellAddress cellAddress = sheet.getCellByPosition(startCol - shiftNumber, startRow).getCellAddress();
//			sheet.moveRange(cellAddress, new CellRangeAddress(cellAddress.Sheet, startCol, startRow, endCol, endRow));
//		} catch (IndexOutOfBoundsException e) {
//			e.printStackTrace();
//		}
//		for (int i = startRow; i <= endRow; i++) {
//			boolean doSetWidth = true;
//			int row = sheet.getRow(i);
//			if (row != null) {
//				for (int j = startCol; j <= endCol; j++) {
//					HSSFCell cell = row.getCell(j);
//					if (cell == null) {
//						cell = row.createCell(j);
//						doSetWidth = false;
//					}
//					HSSFCell destCell = row.getCell((int) (j - shiftNumber));
//					if (destCell == null) {
//						destCell = row.createCell((int) (j - shiftNumber));
//					}
//					copyCell(cell, destCell, true);
//					if (doSetWidth) {
//						sheet.setColumnWidth(destCell.getColumnIndex(),
//								getWidth(sheet, cell.getColumnIndex()));
//					}
//				}
//			}
//		}
	}

	static long getWidth(OdfTable sheet, int col) {
		return sheet.getColumnByIndex(col).getWidth();
	}

//	public static void shiftCellsRight(OdfTable sheet, int startRow,
//			int endRow, int startCol, int shiftNumber) {
////		try {
//			OdfTableRow currentRow = sheet.getRowByIndex(startRow);
//			int oldCellcount = sheet.getColumnCount();
//			for (int i=0;i<shiftNumber;i++)
//				sheet.appendColumn();
//			while (currentRow != null && currentRow.getRowIndex() <= endRow) {
//				for (int i=oldCellcount-1;i>=startCol;i--)
//					copyCell(currentRow.getCellByIndex(i), currentRow.getCellByIndex(i + shiftNumber), true);
//				currentRow = currentRow.getNextRow();
//			}
////			CellAddress cellAddress = sheet.getCellByPosition(startCol + shiftNumber, startRow).getCellAddress();
////			sheet.copyRange(cellAddress, new CellRangeAddress(cellAddress.Sheet, startCol, startRow, sheet.getLastColNum(), endRow));
////		} catch (IndexOutOfBoundsException e) {
////			e.printStackTrace();
////		}
////		for (int i = startRow; i <= endRow; i++) {
////			com.bfinance.framework.openoffice.Row row = sheet.getRow(i);
////			if (row != null) {
////				int lastCellNum = row.getLastCell().getCellAddress().Column;
////				for (int j = lastCellNum; j >= startCol; j--) {
////					com.bfinance.framework.openoffice.Cell destCell = row.getCell((int) (j + shiftNumber));
////					if (destCell == null) {
////						destCell = row.createCell((int) (j + shiftNumber));
////					}
////					HSSFCell cell = row.getCell(j);
////					if (cell == null) {
////						cell = row.createCell(j);
////					}
////					copyCell(cell, destCell, true);
////				}
////			}
////		}
//	}

	public static void updateCellValue(OdfTable sheet, int rowNum, int colNum,
			String cellValue) {
		try {
			sheet.getCellByPosition(colNum, rowNum).setStringValue(cellValue);
		} catch (IndexOutOfBoundsException e) {
			e.printStackTrace();
		}
	}

//	public static void copyPageSetup(Sheet destSheet, Sheet srcSheet) {
//		XHeaderFooter headerFooter;
//		headerFooter.
//		HSSFHeader header = srcSheet.getHeader();
//		HSSFFooter footer = srcSheet.getFooter();
//		if (footer != null) {
//			destSheet.getFooter().setLeft(footer.getLeft());
//			destSheet.getFooter().setCenter(footer.getCenter());
//			destSheet.getFooter().setRight(footer.getRight());
//		}
//		if (header != null) {
//			destSheet.getHeader().setLeft(header.getLeft());
//			destSheet.getHeader().setCenter(header.getCenter());
//			destSheet.getHeader().setRight(header.getRight());
//		}
//	}
//
//	public static void copyPrintSetup(XSpreadsheet destSheet, XSpreadsheet srcSheet) {
//		HSSFPrintSetup setup = srcSheet.getPrintSetup();
//		if (setup != null) {
//			destSheet.getPrintSetup().setLandscape(setup.getLandscape());
//			destSheet.getPrintSetup().setPaperSize(setup.getPaperSize());
//			destSheet.getPrintSetup().setScale(setup.getScale());
//			destSheet.getPrintSetup().setFitWidth(setup.getFitWidth());
//			destSheet.getPrintSetup().setFitHeight(setup.getFitHeight());
//			destSheet.getPrintSetup().setFooterMargin(setup.getFooterMargin());
//			destSheet.getPrintSetup().setHeaderMargin(setup.getHeaderMargin());
//			destSheet.getPrintSetup().setPaperSize(setup.getPaperSize());
//			destSheet.getPrintSetup().setPageStart(setup.getPageStart());
//		}
//	}
//
//	public static void setPrintArea(HSSFWorkbook resultWorkbook, int sheetNum) {
//		int maxColumnNum = 0;
//		for (int j = resultWorkbook.getSheetAt(sheetNum).getFirstRowNum(); j <= resultWorkbook
//				.getSheetAt(sheetNum).getLastRowNum(); j++) {
//			int row = resultWorkbook.getSheetAt(sheetNum).getRow(j);
//			if (row != null) {
//				maxColumnNum = row.getLastCellNum();
//			}
//		}
//		resultWorkbook.setPrintArea(sheetNum, 0, maxColumnNum, 0,
//				resultWorkbook.getSheetAt(sheetNum).getLastRowNum());
//	}

	protected static final String regexCellRef = "[a-zA-Z]+[0-9]+";
	protected static final Pattern regexCellRefPattern = Pattern
			.compile(regexCellRef);
	protected static final String regexCellCharPart = "[0-9]+";
	protected static final String regexCellDigitPart = "[a-zA-Z]+";
	protected static String cellRangeSeparator = ":";

	public static boolean isColumnRange(List<String> cells) {
		String firstCell = cells.get(0);
		boolean isColumnRange = true;
		if (firstCell != null && firstCell.length() > 0) {
			String firstCellCharPart = firstCell.split(regexCellCharPart)[0];
			String firstCellDigitPart = firstCell.split(regexCellDigitPart)[1];
			int cellNumber = Integer.parseInt(firstCellDigitPart);
			String nextCell, cellCharPart, cellDigitPart;
			for (int i = 1; i < cells.size() && isColumnRange; i++) {
				nextCell = (String) cells.get(i);
				cellCharPart = nextCell.split(regexCellCharPart)[0];
				cellDigitPart = nextCell.split(regexCellDigitPart)[1];
				if (!firstCellCharPart.equalsIgnoreCase(cellCharPart)
						|| Integer.parseInt(cellDigitPart) != ++cellNumber) {
					isColumnRange = false;
				}
			}
		}
		return isColumnRange;
	}

//	public static boolean isRowRange(List cells) {
//		String firstCell = (String) cells.get(0);
//		boolean isRowRange = true;
//		if (firstCell != null && firstCell.length() > 0) {
//			String firstCellDigitPart = firstCell.split(regexCellDigitPart)[1];
//			String nextCell, cellDigitPart;
//			CellReference cellRef = new CellReference(firstCell);
//			int cellNumber = cellRef.getCol();
//			for (int i = 1; i < cells.size() && isRowRange; i++) {
//				nextCell = (String) cells.get(i);
//				cellDigitPart = nextCell.split(regexCellDigitPart)[1];
//				cellRef = new CellReference(nextCell);
//				if (!firstCellDigitPart.equalsIgnoreCase(cellDigitPart)
//						|| cellRef.getCol() != ++cellNumber) {
//					isRowRange = false;
//				}
//			}
//		}
//		return isRowRange;
//	}

	public static String buildCommaSeparatedListOfCells(String refSheetName,
			List<String> cells) {
		String listOfCells = "";
		for (String cell : cells) {
			listOfCells += getRefCellName(refSheetName, cell) + ",";
		}
		listOfCells += getRefCellName(refSheetName, (String) cells.get(cells
				.size() - 1));
		return listOfCells;
	}

//	public static String detectCellRange(String refSheetName, List cells) {
//		if (cells == null || cells.isEmpty()) {
//			return "";
//		}
//		String firstCell = (String) cells.get(0);
//		String range = firstCell;
//		if (firstCell != null && firstCell.length() > 0) {
//			if (isRowRange(cells) || isColumnRange(cells)) {
//				String lastCell = (String) cells.get(cells.size() - 1);
//				range = getRefCellName(refSheetName, firstCell)
//						+ cellRangeSeparator + lastCell.toUpperCase();
//			} else {
//				range = buildCommaSeparatedListOfCells(refSheetName, cells);
//			}
//		}
//		return range;
//	}

	public static String getRefCellName(String refSheetName, String cellName) {
		if (refSheetName == null) {
			return cellName.toUpperCase();
		}
		return refSheetName + "." + cellName.toUpperCase();
	}

//	public static void shiftRows(OdfTable sheet, int startRow, int endRow,
//			int shiftNum) {
////		CellAddress cellAddress = new CellAddress();
////		cellAddress.Column = 0;
////		cellAddress.Row = startRow + shiftNum;
////		XCellRangeAddressable cellRangeAddressable = (XCellRangeAddressable)UnoRuntime.queryInterface(XCellRangeAddressable.class, sheet.getCellRangeByPosition(sheet.getFirstColNum(), startRow, sheet.getLastColNum(), endRow));
////		sheet.moveRange(cellAddress, cellRangeAddressable.getRangeAddress());
//		if (startRow <= endRow) {
//			long[] rowHeights = getRowHeights(sheet, startRow, endRow);
//			try {
////				CellAddress cellAddress = sheet.getCellByPosition(0, startRow + shiftNum + 1).getCellAddress();
////				sheet.moveRange(cellAddress, new CellRangeAddress(cellAddress.Sheet, 0, startRow, sheet.getLastColNum(), endRow));
//			} catch (IndexOutOfBoundsException e) {
//				e.printStackTrace();
//			}
////			sheet.shiftRows(startRow, endRow, shiftNum, false, false);
//			copyPositiveRowHeight(sheet, startRow, endRow, shiftNum, rowHeights);
//		}
//	}

//	private static long[] getRowHeights(OdfTable sheet, int startRow,
//			int endRow) {
//		if (endRow - startRow + 1 < 0) {
//			return new long[0];
//		}
//		long[] rowHeights = new long[endRow - startRow + 1];
//		for (int i = startRow; i <= endRow; i++) {
//			OdfTableRow row = sheet.getRowByIndex(i);
//			if (row != null) {
//				rowHeights[i - startRow] = row.getHeight();
//			} else {
//				rowHeights[i - startRow] = -1;
//			}
//		}
//		return rowHeights;
//	}

	static void copyPositiveRowHeight(OdfTable sheet, int startRow,
			int endRow, int shiftNum, long[] rowHeights) {
		for (int i = startRow; i <= endRow; i++) {
			OdfTableRow destRow = sheet.getRowByIndex(i + shiftNum);
			if (destRow != null && rowHeights[i - startRow] >= 0) {
				destRow.setHeight(rowHeights[i - startRow], true);
			}
			// int srcRow = sheet.getRow(i);
			// if( srcRow != null && destRow != null ){
			// if( srcRow.getHeight() >= 0 ){
			// destRow.setHeight( srcRow.getHeight() );
			// }
			// }
		}
	}
}
